﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using bdxyit.com.RecruitmentAppSys.Models;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：FamilyInfoModels
*系统时间：3/25/2012 3:35:34 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:35:34 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class FamilyInfoDAL : IFamilyInfoDAL
    {
        //静态常量
        private const string SQL_SELECT_FAMILYINFO_BY_USERID_AND_FID = " select  ID,FamilyName,Relationship,Job,SectorPosts,Address,UserID from FamilyInfos "
                                                                         + "  where UserID=@UserID and ID=@ID";
        private const string SQL_SELECT_FAMILYINFO_BY_USERID = " select  ID,FamilyName,Relationship,Job,SectorPosts,Address,UserID from FamilyInfos "
                                                                 + "  where UserID=@UserID";
        private const string SQL_INSERT_FAMILYINFO = " insert into FamilyInfos("
                                                    + " ID,FamilyName,Relationship,Job,SectorPosts,Address,UserID)"
                                                    + "  values ("
                                                    + " @ID,@FamilyName,@Relationship,@Job,@SectorPosts,@Address,@UserID)";
        private const string SQL_UPDATE_FAMILYINFO_BY_USERID = " update FamilyInfos set "
                                                            + " ID=@ID,"
                                                            + " FamilyName=@FamilyName,"
                                                            + " Relationship=@Relationship,"
                                                            + " Job=@Job,"
                                                            + " SectorPosts=@SectorPosts,"
                                                            + " Address=@Address,"
                                                            + " UserID=@UserID"
                                                            + "  where UserID=@UserID";
        private const string SQL_DELETE_FAMILYINFO = " delete from FamilyInfos "
                                                   + "  where UserID=@UserID";
        private const string FIELD_FamilyName = "@FamilyName";
        private const string FIELD_Relationship = "@Relationship";
        private const string FIELD_Job = "@Job";
        private const string FIELD_SectorPosts = "@SectorPosts";
        private const string FIELD_Address = "@Address";
        private const string FIELD_UserID = "@UserID";
        private const string FIELD_FID = "@ID";

        /// <summary>
        /// 插入的家庭信息
        /// </summary>
        /// <returns></returns>
        public bool InsertFamilyInfoList(FamilyInfo fi)
        {
            bool flag = false;

            SqlParameter[] FamilyInfoParms = GetFamilyInfoParameters();
            SqlCommand cmd = new SqlCommand();
            FamilyInfoParms[0].Value = fi.FamilyName;
            FamilyInfoParms[1].Value = fi.Relationship;
            FamilyInfoParms[2].Value = fi.Job;
            FamilyInfoParms[3].Value = fi.SectorPosts;
            FamilyInfoParms[4].Value = fi.Address;
            FamilyInfoParms[5].Value = fi.UserID;
            foreach (SqlParameter parm in FamilyInfoParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_FAMILYINFO;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 获取所有的家庭信息通过用户Id
        /// </summary>
        /// <returns></returns>
        public List<FamilyInfo> SelectFamilyInfoByUserId(string UserId)
        {
            List<FamilyInfo> FamilyInfo = new List<FamilyInfo>();

            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 128);
            parm.Value = UserId;

            //执行读取家庭成员
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_FAMILYINFO_BY_USERID, parm))
            {
                if (rdr.Read())
                {
                    FamilyInfo fi = new FamilyInfo();
                    if (rdr["ID"] != null && rdr["ID"].ToString() != "")
                    {
                        fi.ID = int.Parse(rdr["ID"].ToString());
                    }
                    if (rdr["FamilyName"] != null && rdr["FamilyName"].ToString() != "")
                    {
                        fi.FamilyName = rdr["FamilyName"].ToString();
                    }
                    if (rdr["Relationship"] != null && rdr["Relationship"].ToString() != "")
                    {
                        fi.Relationship = rdr["Relationship"].ToString();
                    }
                    if (rdr["Job"] != null && rdr["Job"].ToString() != "")
                    {
                        fi.Job = rdr["Job"].ToString();
                    }
                    if (rdr["SectorPosts"] != null && rdr["SectorPosts"].ToString() != "")
                    {
                        fi.SectorPosts = rdr["SectorPosts"].ToString();
                    }
                    if (rdr["Address"] != null && rdr["Address"].ToString() != "")
                    {
                        fi.Address = rdr["Address"].ToString();
                    }
                    if (rdr["UserID"] != null && rdr["UserID"].ToString() != "")
                    {
                        fi.UserID = rdr["UserID"].ToString();
                    }
                    FamilyInfo.Add(fi);
                }
            }
            return FamilyInfo;
        }

        public FamilyInfo SelectFamilyInfoByUserIdAndFid(string userId, string fid)
        {
            FamilyInfo familyInfo = null;

            //创建参数
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 18), new SqlParameter(FIELD_FID, SqlDbType.Int) };
            parm[0].Value = userId;
            parm[1].Value = fid;

            //执行读取家庭成员
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_FAMILYINFO_BY_USERID_AND_FID, parm))
            {
                if (rdr.Read())
                {
                    familyInfo = new FamilyInfo();

                    if (rdr["ID"] != null && rdr["ID"].ToString() != "")
                    {
                        familyInfo.ID = int.Parse(rdr["ID"].ToString());
                    }
                    if (rdr["FamilyName"] != null && rdr["FamilyName"].ToString() != "")
                    {
                        familyInfo.FamilyName = rdr["FamilyName"].ToString();
                    }
                    if (rdr["Relationship"] != null && rdr["Relationship"].ToString() != "")
                    {
                        familyInfo.Relationship = rdr["Relationship"].ToString();
                    }
                    if (rdr["Job"] != null && rdr["Job"].ToString() != "")
                    {
                        familyInfo.Job = rdr["Job"].ToString();
                    }
                    if (rdr["SectorPosts"] != null && rdr["SectorPosts"].ToString() != "")
                    {
                        familyInfo.SectorPosts = rdr["SectorPosts"].ToString();
                    }
                    if (rdr["Address"] != null && rdr["Address"].ToString() != "")
                    {
                        familyInfo.Address = rdr["Address"].ToString();
                    }
                    if (rdr["UserID"] != null && rdr["UserID"].ToString() != "")
                    {
                        familyInfo.UserID = rdr["UserID"].ToString();
                    }
                }
            }
            return familyInfo;
        }

        /// <summary>
        /// 更新家庭信息
        /// </summary>
        /// <param name="fi"></param>
        /// <returns></returns>
        public bool UpdateFamilyInfo(FamilyInfo fi)
        {
            bool flag = false;

            SqlParameter[] FamilyInfoParms = GetFamilyInfoParameters();
            SqlCommand cmd = new SqlCommand();
            FamilyInfoParms[0].Value = fi.FamilyName;
            FamilyInfoParms[1].Value = fi.Relationship;
            FamilyInfoParms[2].Value = fi.Job;
            FamilyInfoParms[3].Value = fi.SectorPosts;
            FamilyInfoParms[4].Value = fi.Address;
            FamilyInfoParms[5].Value = fi.UserID;
            foreach (SqlParameter parm in FamilyInfoParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_FAMILYINFO_BY_USERID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 删除家庭信息
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public bool DeleteFamilyInfoByUserId(string UserId)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.Int);
            parm.Value = UserId;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_FAMILYINFO;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 获取家庭成员参数
        /// </summary>
        /// <returns></returns>
        private SqlParameter[] GetFamilyInfoParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
					new SqlParameter(FIELD_FamilyName, SqlDbType.VarChar, 30),
					new SqlParameter(FIELD_Relationship, SqlDbType.VarChar, 128),
					new SqlParameter(FIELD_Job, SqlDbType.VarChar, 50),
					new SqlParameter(FIELD_SectorPosts, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_Address, SqlDbType.VarChar, 100),
					new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 120)
            };
            return parms;
        }
    }
}